package com.example.dps;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.drawable.Drawable;
import android.util.Log;

/**
 * Class to help keep track of data on phone. Stores in local SQL database
 * DONT USE FOR ACCOUNT INFO
 * @author merrickc
 *
 */
public class DBHelper extends SQLiteOpenHelper{

	public SQLiteDatabase db = null;
	
	public Context context = null;
	
	
	/**********************************************************************
	 * Database Variables
	 */
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "DownPaymentSaver.db";
    /********************************************************************/
    
    
    
    /**********************************************************************
     * Goal Table Variables
     */
    public final static String USER_TABLE_NAME = "User";
    public static final String COLUMN_TOTAL = "total";
    public static final String COLUMN_SAVED = "saved";
    public static final String COLUMN_DEADLINE = "deadline";
    public static final String COLUMN_IMAGE = "image";
    public static final String COLUMN_BONUS_POINTS = "points";
    public static final String COLUMN_HOME_PRICE = "price";
    public static final String COLUMN_LAST_CONTRIBUTION_DATE = "lastDeposit";
    public static final String COLUMN_ENABLE_NOTIFICATIONS = "enableNotifications";
    public static final String COLUMN_MADE_DEPOSIT = "madeFirstDeposit";
    public static final String COLUMN_ACCOUNT_CREATED = "accountCreated";
    public static final String COLUMN_FIRST_TIME = "firstTime";
    public static final String COLUMN_LAST_REMINDED = "lastReminded";
    public static final String COLUMN_USERNAME = "username";
    //public static final String COLUMN_PASSWORD = "password";
    public static final String COLUMN_EMAIL = "email";
    public static final String COLUMN_REMEMBER_ME = "rememberMe";
    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_ACH_CONFIRMED = "achConfirmed";
    //public static final String COLUMN_SESSION_TOKEN = "sessionToken";
    
    static Date dateNow = new Date();
    static SimpleDateFormat dateformatMMDDYYYY = new SimpleDateFormat("MM/dd/yyyy");
    static StringBuilder nowMMDDYYYY = new StringBuilder( dateformatMMDDYYYY.format( dateNow ) );
    
    
    
    public static final String CREATE_GOAL_TABLE = "CREATE TABLE IF NOT EXISTS " + USER_TABLE_NAME +
    		" (" + 
    		COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
    		COLUMN_TOTAL + " INTEGER DEFAULT 7000," +
    		COLUMN_SAVED + " INTEGER DEFAULT 0," +
    		COLUMN_DEADLINE + " TEXT," +
    		COLUMN_IMAGE + " TEXT," +
    		COLUMN_BONUS_POINTS + " INTEGER," +
    		COLUMN_HOME_PRICE + " INTEGER DEFAULT 200000," +
    		COLUMN_LAST_CONTRIBUTION_DATE + " TEXT DEFAULT '" + nowMMDDYYYY.toString() + "',"+
    		//COLUMN_KEEP_LOGGED_IN + " INTEGER DEFAULT 0," +
    		COLUMN_ENABLE_NOTIFICATIONS + " INTEGER DEFAULT 1," +
    		COLUMN_MADE_DEPOSIT + " INTEGER DEFAULT 0," + 
    		COLUMN_ACH_CONFIRMED + " INTEGER DEFAULT 0," + 
    		COLUMN_ACCOUNT_CREATED + " INTEGER DEFAULT 0," + 
    		COLUMN_FIRST_TIME + " INTEGER DEFAULT 1," +
    		COLUMN_LAST_REMINDED + " TEXT DEFAULT ''," +
    		COLUMN_USERNAME + " VARCHAR(60)," + 
    		//COLUMN_PASSWORD + " VARCHAR(80)," +
    		COLUMN_REMEMBER_ME + " INTEGER DEFAULT 0,"+
    		COLUMN_EMAIL + " VARCHAR(60)" +
    		
    		//COLUMN_SESSION_TOKEN + " VARCHAR(60)" +
    		");";
	/**** End Goal Table Variables ****************************************/
    
    

    
    

	public DBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		// TODO Auto-generated constructor stub
		
		this.context = context;
		
		this.db = getWritableDatabase();
		
		//db.execSQL("DROP TABLE IF EXISTS "+ USER_TABLE_NAME);
		this.db.execSQL(CREATE_GOAL_TABLE);
		
		ContentValues values = new ContentValues();
		values.put(COLUMN_SAVED, 0);
		
		if(this.isTableEmpty()){
			this.db.insert(USER_TABLE_NAME, null, values);
		}
		
		db.close();
		
		
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		/** create the tables if they don't exist **/
		

		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
	
	/**
	 * inserts a given goal entry. there should only be 1 goal entry!
	 * @param values
	 */
	public void insertGoalEntry(ContentValues values){
	
		SQLiteDatabase db = this.getWritableDatabase();
		
		if(this.isTableEmpty()){
			db.insert(USER_TABLE_NAME,null, values);
			Log.e("DB QUERY", "INSERTED GOAL ENTRY");
		}
		else{
			new Throwable("Can't insert more than 1 goal into goal DB");
		}
		
		db.close();
	
	}
	
	
	/**
	 * updates the Goal entry with the given content values
	 * @param values
	 */
	public void updateGoalEntry(ContentValues values){
		
		SQLiteDatabase db = this.getWritableDatabase();
		
		db.update(USER_TABLE_NAME,values, null, null);
		
		db.close();
		
	}
	
	/**
	 * returns the goal total to save for
	 * @return 
	 */
	int selectGoalTotal(){
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		Cursor cursor = db.rawQuery("SELECT * FROM " + USER_TABLE_NAME, null);
		if(cursor.moveToFirst()){
			int total = cursor.getInt(cursor.getColumnIndex(COLUMN_TOTAL));
			Log.e("DB QUERY", total + " = total");
			
			cursor.close();
			db.close();
			
			return total;
		}
		else Log.e("DB QUERY", "GOAL CURSOR IS EMPTY");
		
		cursor.close();
		db.close();
		
		return -1;

	}
	
	/**
	 * returns the amount of money saved so far towards the goal
	 * @return
	 */
	double selectGoalSaved(){
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		Cursor cursor = db.rawQuery("SELECT * FROM " + USER_TABLE_NAME, null);
		if(cursor.moveToFirst()){
			double saved = cursor.getDouble(cursor.getColumnIndex(COLUMN_SAVED));
			Log.e("DB QUERY", saved + " = saved");
			
			cursor.close();
			db.close();
			return saved;
		}
		else Log.e("DB QUERY", "GOAL CURSOR IS EMPTY");
		
		cursor.close();
		db.close();
		
		return -1;

	}
	
	/**
	 * returns the goal in the goal DB
	 * @return returns a cursor containing the goal entry
	 */
	Cursor selectGoal(){
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		Cursor cursor = db.rawQuery("SELECT * FROM " + USER_TABLE_NAME, null);
		
		//db.close();
		
		return cursor;

	}
	
	/**
	 * returns true if the given table is empty
	 * @param tableName String representing the table name
	 * @return true if the table is empty
	 */
	public boolean isTableEmpty(){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery("SELECT * FROM " + USER_TABLE_NAME, null);
		if(cursor.getCount() > 0){
			//db.close();
			return false;
		}
		else{
			//db.close();
			return true;
		}
		
		
	
	}

	/**
	 * get the goal deadline in string format
	 * @return returns the deadline in "MM/dd/yyyy" format
	 */
	public String selectGoalDeadline() {
		SQLiteDatabase db = this.getReadableDatabase();
		
		Cursor cursor = db.rawQuery("SELECT * FROM " + USER_TABLE_NAME, null);
		if(cursor.moveToFirst()){
			String deadline = cursor.getString(cursor.getColumnIndex(COLUMN_DEADLINE));
			Log.e("DB QUERY", deadline + " = deadline");
			//db.close();
			return deadline;
		}
		else Log.e("DB QUERY", "GOAL CURSOR IS EMPTY");
		//db.close();
		return null;
	}
	
	
	
	public void updateHomePrice(int homePrice){
		
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		
		values.put(COLUMN_HOME_PRICE, homePrice);
		
		db.update(USER_TABLE_NAME,values, null, null);
		
		//db.close();
	}
	
	
	public int selectHomePrice(){
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		Cursor cursor = db.rawQuery("SELECT * FROM " + USER_TABLE_NAME, null);
		if(cursor.moveToFirst()){
			
			if(cursor.getColumnIndex(COLUMN_HOME_PRICE) == -1) return 0;
			
			int homePrice = cursor.getInt(cursor.getColumnIndex(COLUMN_HOME_PRICE));
			
			cursor.close();
			db.close();
			
			return homePrice;
		}
		cursor.close();
		db.close();
		
		return 0;
		
	}
	
	public void updateDownPaymentGoal(int downPayment){
		
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		
		values.put(COLUMN_TOTAL, downPayment);
		
		db.update(USER_TABLE_NAME,values, null, null);
		
		//db.close();
		
	}
	
	public void updateImage(String imgLoc){
		
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		
		Log.e("DBHELPER", "image location = " + imgLoc);
		
		values.put(COLUMN_IMAGE, imgLoc);
		
		if(db.update(USER_TABLE_NAME,values, null, null) > 0){
			Log.e("DBHElper", "atleast 1 row affected");
		}
		else{
			Log.e("DBHELPER", "NO ROWS AFFECTED updateImage");
		}
		
		db.close();
		
		
	}
	
	public boolean hasCustomImage(){
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		String imagePath = "";
		
		Cursor cursor = db.rawQuery("SELECT image FROM " + USER_TABLE_NAME, null);
		if(cursor.moveToFirst()){
			imagePath = cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE));
		}
		
		try{
			Drawable image;
			
			//Log.e("DBHELPER", "IMAGE PATH +++== " + imagePath);
			Integer id = Integer.parseInt(imagePath);
			
			
		}
		catch(NumberFormatException e){
			return true;
		}
		
		
		cursor.close();
		db.close();
		
		
		return false;
		
		
	}
	
	public Drawable getImage(){
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		String imagePath = "";
		
		Drawable image = null;
		
		
		Cursor cursor = db.rawQuery("SELECT * FROM " + USER_TABLE_NAME, null);
		if(cursor.moveToFirst()){
			//Log.e("DBHELPER", "movingToFirst getImage()");
			//Log.e("DBHELPER", "PRE GETSTRING == " + imagePath);
			imagePath = cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE));
			//Log.e("DBHELPER", imagePath + " ");
		}
		
		try{
			
			
			//Log.e("DBHELPER", "IMAGE PATH +++== " + imagePath);
			
			Integer id = R.drawable.house_2;
			try{
				id = Integer.parseInt(imagePath);
			}
			catch(NumberFormatException e){
				
				image = Drawable.createFromPath(imagePath);
				
				
			}
			try{
				image = this.context.getResources().getDrawable(id);
				
				cursor.close();
				db.close();
				
				//Log.e("dbhelper", "getting image from ID");
				
				return image;
			}
			catch(Exception e){
				e.printStackTrace();
			}
			
		}
		catch(NumberFormatException e){
			e.printStackTrace();
		}
		
		cursor.close();
		db.close();
		
		//Drawable image = Drawable.createFromPath(imagePath);
		
		//Log.e("dbhelper", "getting image from path");
		
		return image;
		
		
	}
	
	
	public String getImagePath(){
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		String imagePath = "";
		
		Cursor cursor = db.rawQuery("SELECT * FROM " + USER_TABLE_NAME, null);
		if(cursor.moveToFirst()){
			imagePath = cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE));
		}
		
		cursor.close();
		db.close();
		
		return imagePath;
		
		
	}
	
	public boolean getBoolean(String columnName){
		
		this.db = this.getReadableDatabase();
		
		Cursor cursor = db.rawQuery("SELECT "+columnName+" FROM " + USER_TABLE_NAME, null);
		if(cursor.moveToFirst()){
			
			if(cursor.getColumnIndex(columnName) == -1) new Throwable("column Does not exist!, getBoolean(columnName)" );
			
			int result = cursor.getInt(cursor.getColumnIndex(columnName));
			
			if(result == 1){
				
				cursor.close();
				this.db.close();
				return true;
			}
			
			cursor.close();
			this.db.close();
			return false;
		}
		cursor.close();
		this.db.close();
		return false;
		
		
	}
	
	public void setBoolean(String columnName, int value){
		
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		values.put(columnName, value);
		
		db.update(this.USER_TABLE_NAME, values, null, null);
		
		db.close();
		
		
	}
	
	public Date getLastDeposit(){
		
		this.db = getReadableDatabase();
		
		Cursor cursor = db.rawQuery("SELECT " + COLUMN_LAST_CONTRIBUTION_DATE + " FROM " + this.USER_TABLE_NAME, null);
		
		String dateString = null;
		Date date = null;
		
		if(cursor.moveToFirst()){
			dateString = cursor.getString(0);
			
			//if date length = 7, then add a 0 to get the format correct. ie 2/09/1990 --> 02091990
			
			
			try {
				date = new SimpleDateFormat("MM/dd/yyyy").parse(dateString);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			//Log.e("DBHelper", "Last deposit date = " +date.toString());
			
			this.db.close();
			
			return date;
			
		}
		
		Log.e("date = ", date.toString());
		
		this.db.close();
		
		return null;
		
	}

	public void setLastDeposit() {
		// TODO Auto-generated method stub
		
		this.db = getWritableDatabase();
		
		ContentValues values = new ContentValues();
		
		
		String date = new SimpleDateFormat("MM/dd/yyyy").format(new Date());
		
		Log.e("set date", date);
		
		
		values.put(COLUMN_LAST_CONTRIBUTION_DATE, date);
		
		this.db.update(USER_TABLE_NAME, values, null, null);
		
	}
	
	
	public void setLastReminded(){
		
		this.db = getWritableDatabase();
		
		ContentValues values = new ContentValues();
		
		String date = new SimpleDateFormat("MM/dd/yyyy").format(new Date());
		
		values.put(COLUMN_LAST_REMINDED, date);
		
		this.db.update(USER_TABLE_NAME, values, null, null);
		
		this.db.close();
		
	}

	public Date getLastReminded(){
		
		this.db = getReadableDatabase();
		
		String query = "SELECT " + COLUMN_LAST_REMINDED  + " FROM " + USER_TABLE_NAME + ";";
		
		Cursor result = this.db.rawQuery(query, null);
		
		if(result.moveToFirst()){
			String strDate = result.getString(0);
			Date date = null;
			
			result.close();
			
			this.db.close();
			
			try {
				if(strDate.equals("")) return date;
				date = new SimpleDateFormat("MM/dd/yyyy").parse(strDate);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
			return date;
		}
		
		return null;
		
	}
	

	
//	/**
//	 * returns the users Session Token if he is logged in. Null otherwise
//	 * @return
//	 */
//	public String getSessionToken() {
//		
//		this.db = getReadableDatabase();
//		
//		String query = "SELECT " + COLUMN_SESSION_TOKEN  + " FROM " + USER_TABLE_NAME + ";";
//		
//		Cursor result = this.db.rawQuery(query, null);
//		
//		result.moveToFirst();
//		
//		String token = result.getString(0);
//
//		if(token.equals("")) token = null;
//		
//		result.close();
//		
//		this.db.close();
//		
//		return token;	
//	}
	
	/**
	 * returns the users username if he is logged in.
	 * @return
	 */
	public String getUsername() {
		
		this.db = getReadableDatabase();
		
		String query = "SELECT " + COLUMN_USERNAME  + " FROM " + USER_TABLE_NAME + ";";
		
		Cursor result = this.db.rawQuery(query, null);
		
		result.moveToFirst();
		
		String token = result.getString(0);

		if(token == null || token.equals("")) token = null;
		
		result.close();
		
		this.db.close();
		
		return token;	
	}
	
	public int getUserID() {
		
		this.db = getReadableDatabase();
		
		String query = "SELECT " + COLUMN_ID  + " FROM " + USER_TABLE_NAME + ";";
		
		Cursor result = this.db.rawQuery(query, null);
		
		result.moveToFirst();
		
		int id = result.getInt(0);
		
		result.close();
		
		this.db.close();
		
		return id;	
	}
	
	public void setUsername(String username){
		
		this.db = getWritableDatabase();
		
		ContentValues values = new ContentValues();
		
		values.put(COLUMN_USERNAME, username);
		
		this.db.update(USER_TABLE_NAME, values, null, null);
		
		this.db.close();
		
	}
	
	public void deleteUser(){
		
		this.db=getWritableDatabase();
		
		db.execSQL("DROP TABLE IF EXISTS " + this.USER_TABLE_NAME);
		
		this.db.close();
	}
	

}
